package com.retail.risk.analysis;

import java.sql.*;
import java.util.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
 * 店铺销售风险分析服务类
 */
public class ShopRiskAnalysisService {
    
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=RetailDB;encrypt=true;trustServerCertificate=true";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";
    
    /**
     * 风险等级枚举
     */
    public enum RiskLevel {
        HIGH("高风险"),
        MEDIUM("中风险"),
        LOW("低风险");
        
        private final String description;
        
        RiskLevel(String description) {
            this.description = description;
        }
        
        public String getDescription() {
            return description;
        }
    }
    
    /**
     * 店铺风险评分结果
     */
    public static class RiskScoreResult {
        private int shopId;
        private String shopName;
        private double totalRiskScore;
        private RiskLevel riskLevel;
        private String riskFactors;
        private LocalDate analysisDate;
        
        // 构造函数、getter和setter方法
        public RiskScoreResult(int shopId, String shopName, double totalRiskScore, 
                             RiskLevel riskLevel, String riskFactors, LocalDate analysisDate) {
            this.shopId = shopId;
            this.shopName = shopName;
            this.totalRiskScore = totalRiskScore;
            this.riskLevel = riskLevel;
            this.riskFactors = riskFactors;
            this.analysisDate = analysisDate;
        }
        
        // Getter方法
        public int getShopId() { return shopId; }
        public String getShopName() { return shopName; }
        public double getTotalRiskScore() { return totalRiskScore; }
        public RiskLevel getRiskLevel() { return riskLevel; }
        public String getRiskFactors() { return riskFactors; }
        public LocalDate getAnalysisDate() { return analysisDate; }
    }
    
    /**
     * 风险预警信息
     */
    public static class RiskAlert {
        private int alertId;
        private int shopId;
        private String shopName;
        private String alertType;
        private String alertLevel;
        private String alertMessage;
        private LocalDate alertDate;
        private boolean isResolved;
        
        // 构造函数和getter方法
        public RiskAlert(int alertId, int shopId, String shopName, String alertType,
                        String alertLevel, String alertMessage, LocalDate alertDate, boolean isResolved) {
            this.alertId = alertId;
            this.shopId = shopId;
            this.shopName = shopName;
            this.alertType = alertType;
            this.alertLevel = alertLevel;
            this.alertMessage = alertMessage;
            this.alertDate = alertDate;
            this.isResolved = isResolved;
        }
        
        // Getter方法
        public int getAlertId() { return alertId; }
        public int getShopId() { return shopId; }
        public String getShopName() { return shopName; }
        public String getAlertType() { return alertType; }
        public String getAlertLevel() { return alertLevel; }
        public String getAlertMessage() { return alertMessage; }
        public LocalDate getAlertDate() { return alertDate; }
        public boolean isResolved() { return isResolved; }
    }
    
    /**
     * 计算单个店铺的风险评分
     */
    public RiskScoreResult calculateShopRiskScore(int shopId, LocalDate analysisDate) {
        String sql = "EXEC sp_CalculateShopRiskScore ?, ?";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             CallableStatement stmt = conn.prepareCall(sql)) {
            
            stmt.setInt(1, shopId);
            stmt.setDate(2, Date.valueOf(analysisDate));
            
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
                double totalRiskScore = rs.getDouble("total_risk_score");
                String riskLevelStr = rs.getString("risk_level");
                String riskFactors = rs.getString("risk_factors");
                
                RiskLevel riskLevel = RiskLevel.valueOf(riskLevelStr);
                
                // 获取店铺名称
                String shopName = getShopName(shopId);
                
                return new RiskScoreResult(shopId, shopName, totalRiskScore, 
                                        riskLevel, riskFactors, analysisDate);
            }
            
        } catch (SQLException e) {
            System.err.println("计算店铺风险评分时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
        
        return null;
    }
    
    /**
     * 批量计算所有店铺的风险评分
     */
    public List<RiskScoreResult> calculateAllShopsRiskScore(LocalDate analysisDate) {
        List<RiskScoreResult> results = new ArrayList<>();
        
        // 获取所有活跃店铺
        List<Integer> shopIds = getAllActiveShopIds();
        
        for (Integer shopId : shopIds) {
            RiskScoreResult result = calculateShopRiskScore(shopId, analysisDate);
            if (result != null) {
                results.add(result);
            }
        }
        
        return results;
    }
    
    /**
     * 生成风险预警
     */
    public void generateRiskAlerts(LocalDate analysisDate) {
        String sql = "EXEC sp_GenerateRiskAlerts ?";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             CallableStatement stmt = conn.prepareCall(sql)) {
            
            stmt.setDate(1, Date.valueOf(analysisDate));
            stmt.execute();
            
            System.out.println("风险预警生成完成，分析日期: " + analysisDate);
            
        } catch (SQLException e) {
            System.err.println("生成风险预警时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * 获取高风险店铺列表
     */
    public List<RiskScoreResult> getHighRiskShops(LocalDate analysisDate) {
        List<RiskScoreResult> highRiskShops = new ArrayList<>();
        
        String sql = """
            SELECT srs.shop_id, si.shop_name, srs.total_risk_score, 
                   srs.risk_level, srs.risk_factors, srs.analysis_date
            FROM Shop_Risk_Score srs
            JOIN Shop_Info si ON srs.shop_id = si.shop_id
            WHERE srs.analysis_date = ? AND srs.risk_level = 'HIGH'
            ORDER BY srs.total_risk_score DESC
            """;
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setDate(1, Date.valueOf(analysisDate));
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                int shopId = rs.getInt("shop_id");
                String shopName = rs.getString("shop_name");
                double totalRiskScore = rs.getDouble("total_risk_score");
                RiskLevel riskLevel = RiskLevel.valueOf(rs.getString("risk_level"));
                String riskFactors = rs.getString("risk_factors");
                LocalDate date = rs.getDate("analysis_date").toLocalDate();
                
                RiskScoreResult result = new RiskScoreResult(shopId, shopName, totalRiskScore,
                                                          riskLevel, riskFactors, date);
                highRiskShops.add(result);
            }
            
        } catch (SQLException e) {
            System.err.println("获取高风险店铺时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
        
        return highRiskShops;
    }
    
    /**
     * 获取风险预警列表
     */
    public List<RiskAlert> getRiskAlerts(LocalDate startDate, LocalDate endDate) {
        List<RiskAlert> alerts = new ArrayList<>();
        
        String sql = """
            SELECT ra.alert_id, ra.shop_id, si.shop_name, ra.alert_type, 
                   ra.alert_level, ra.alert_message, ra.alert_date, ra.is_resolved
            FROM Risk_Alert ra
            JOIN Shop_Info si ON ra.shop_id = si.shop_id
            WHERE ra.alert_date BETWEEN ? AND ?
            ORDER BY ra.alert_date DESC, ra.alert_level DESC
            """;
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setDate(1, Date.valueOf(startDate));
            stmt.setDate(2, Date.valueOf(endDate));
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                int alertId = rs.getInt("alert_id");
                int shopId = rs.getInt("shop_id");
                String shopName = rs.getString("shop_name");
                String alertType = rs.getString("alert_type");
                String alertLevel = rs.getString("alert_level");
                String alertMessage = rs.getString("alert_message");
                LocalDate alertDate = rs.getDate("alert_date").toLocalDate();
                boolean isResolved = rs.getBoolean("is_resolved");
                
                RiskAlert alert = new RiskAlert(alertId, shopId, shopName, alertType,
                                              alertLevel, alertMessage, alertDate, isResolved);
                alerts.add(alert);
            }
            
        } catch (SQLException e) {
            System.err.println("获取风险预警时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
        
        return alerts;
    }
    
    /**
     * 获取店铺销售趋势分析
     */
    public Map<String, Object> getShopSalesTrend(int shopId, int days) {
        Map<String, Object> trendData = new HashMap<>();
        
        String sql = """
            SELECT sale_date, total_amount, total_quantity, customer_count, return_amount
            FROM Sales_Data
            WHERE shop_id = ? AND sale_date >= DATEADD(DAY, -?, GETDATE())
            ORDER BY sale_date
            """;
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, shopId);
            stmt.setInt(2, days);
            ResultSet rs = stmt.executeQuery();
            
            List<String> dates = new ArrayList<>();
            List<Double> sales = new ArrayList<>();
            List<Integer> quantities = new ArrayList<>();
            List<Integer> customers = new ArrayList<>();
            List<Double> returns = new ArrayList<>();
            
            while (rs.next()) {
                dates.add(rs.getDate("sale_date").toString());
                sales.add(rs.getDouble("total_amount"));
                quantities.add(rs.getInt("total_quantity"));
                customers.add(rs.getInt("customer_count"));
                returns.add(rs.getDouble("return_amount"));
            }
            
            trendData.put("dates", dates);
            trendData.put("sales", sales);
            trendData.put("quantities", quantities);
            trendData.put("customers", customers);
            trendData.put("returns", returns);
            
        } catch (SQLException e) {
            System.err.println("获取销售趋势时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
        
        return trendData;
    }
    
    // 辅助方法
    private String getShopName(int shopId) {
        String sql = "SELECT shop_name FROM Shop_Info WHERE shop_id = ?";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, shopId);
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
                return rs.getString("shop_name");
            }
            
        } catch (SQLException e) {
            System.err.println("获取店铺名称时发生错误: " + e.getMessage());
        }
        
        return "未知店铺";
    }
    
    private List<Integer> getAllActiveShopIds() {
        List<Integer> shopIds = new ArrayList<>();
        
        String sql = "SELECT shop_id FROM Shop_Info WHERE shop_status = 'ACTIVE'";
        
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                shopIds.add(rs.getInt("shop_id"));
            }
            
        } catch (SQLException e) {
            System.err.println("获取活跃店铺列表时发生错误: " + e.getMessage());
        }
        
        return shopIds;
    }
} 